<?php
date_default_timezone_set('PRC');
session_start();
if ($_SESSION["islogin"] !== 'igiveyouthepower') {
    echo "<script language=JavaScript>\r\n";
    echo "alert('请先登录');\r\n";
    echo "location.href='../index.php'\r\n";
    echo "</script>";
}
require_once('../config.php');
$conn = mysqli_connect($cfg_dbhost,$cfg_dbuser,$cfg_dbpwd,$cfg_dbname);

$lang = $_GET["lang"];
$bname = $_GET["bname"];

$customId = $_POST['customId'];
$catePubId = $_POST['catePubId'];
$cateName = $_POST['cateName'];
$cateProperty = $_POST['cateProperty'];
$keywords = $_POST['keywords'];
$grossWeight = $_POST['grossWeight'];
$length = $_POST['length'];
$width = $_POST['width'];
$height = $_POST['height'];
$number = $_POST['number'];
$stockTime = $_POST['stockTime'];
$serviceTemplateID = $_POST['serviceTemplateID'];
$freightTemplateID = $_POST['freightTemplateID'];
$metering = $_POST['metering'];
$salesWay = $_POST['salesWay'];
$norms = $_POST['norms'];
$commodityCode = $_POST['commodityCode'];
$priceSetType = $_POST['priceSetType'];
$priceSellNum = $_POST['priceSellNum'];

if ($_SESSION["thetype"] == "管理员"||$_SESSION["thetype"] == "超级管理员") {
    $sql = "select * from ph_" . $lang . " where 1=1";
} else {
    $sql = "select * from ph_" . $lang . " where 1=1 and user_name='" . $_SESSION["managername"] . "'";
}
if ($bname !== '' && isset($bname)) {
    $sql .= " and bname ='" . $bname . "'";
}
$result = mysqli_query($conn,$sql);
//print_r(mysql_fetch_assoc($result));die;

require '../PHPExcel/PHPExcel.php';
require '../PHPExcel/PHPExcel/Writer/Excel2007.php';

//创建一个excel
$objPHPExcel = new PHPExcel();

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

//设置当前的sheet
$objPHPExcel->setActiveSheetIndex(0);
//设置sheet的name,excel文件名
$fileName = $bname . "-" . date('Ymdhis', time());
//$objPHPExcel->getActiveSheet()->setTitle($fileName);
$headinfo = array(
    '自定义ID',
    '类目ID',
    '类目名称',
    '类目属性',
    '标题',
    '关键词',
    '简短描述',
    '详细描述',
    '货品重（KG）',
    '长(CM)',
    '宽(CM)',
    '高(CM)',
    '备货数量',
    '备货期',
    '服务模板',
    '运费模板',
    '计量单位',
    '销售方式',
    'SKU售价',
    'SKU规格',
    'SKU规格图片',
    '商品编码',
    '价格设置方式',
    '价格区间销售数量',
    '价格区间折扣价格（或折扣比例）',
    '主图',
    '附图1',
    '附图2',
    '附图3',
    '附图4',
    '附图5',
    '附图6',
    '附图7',
    '来源URI',
    '视频地址'
);
//合并单元格
//$objPHPExcel->getActiveSheet()->mergeCells('A1:O1');
//$objPHPExcel->getActiveSheet()->setCellValue('A1', $fileName);
//设置指定单元格宽度
for ($i = 0; $i < count($headinfo); $i++) {
    $objPHPExcel->getActiveSheet()->getColumnDimension(IntToChr($i + 1))->setWidth(20);
}

//水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置起始位置生成表格
$startX = 'A';
$startY = '1';
for ($i = 0; $i < count($headinfo); $i++) {
    //水平居中
    $objPHPExcel->getActiveSheet()->getStyle($startX . $startY)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //垂直居中
    $objPHPExcel->getActiveSheet()->getStyle($startX . $startY)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    //设置column的border
    $objPHPExcel->getActiveSheet()->getStyle($startX . $startY)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
    $objPHPExcel->getActiveSheet()->getStyle($startX . $startY)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle($startX . $startY)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
    //设置border的color
    $objPHPExcel->getActiveSheet()->getStyle($startX . $startY)->getBorders()->getRight()->getColor()->setARGB('000000');
    //设置行高
    //$objPHPExcel->getActiveSheet()->getRowDimension($startY)->setRowHeight(40);
    $objPHPExcel->getActiveSheet()->setCellValue($startX++ . $startY, $headinfo[$i]);
    //设置单元格属性(文本属性)
    $objPHPExcel->getActiveSheet()->getStyle($startX . $startY)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
}
//设置文字颜色
//$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);


$key = 1;
while ($row = mysqli_fetch_array($result)) {
    $key++;
    if (is_array($row) && count($row) > 0) {
        $content = handContent($row['content']);

        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $key, $customId);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $key, $catePubId);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $key, $cateName);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $key, $cateProperty);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $key, $row['proname']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $key, $keywords);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $key, $row['des']);
//        $content=str_replace("\r\n",htmlentities('<br/>'),$row['content']);
//        $content=htmlentities($content);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $key, $content);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $key, $grossWeight);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $key, $length);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . $key, $width);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . $key, $height);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . $key, $number);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . $key, $stockTime);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . $key, $serviceTemplateID);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . $key, $freightTemplateID);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . $key, $metering);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . $key, $salesWay);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . $key, $row['saleprice']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . $key, $norms);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . $key, '');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . $key, $commodityCode);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . $key, $priceSetType);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . $key, $priceSellNum);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . $key, $row['saleprice']);
        //主图
        $sqla = "select * from ph_img where proid='" . $row['ID'] . "' and btid is null and px =0 order by px asc";
        $resulta = mysqli_query($conn,$sqla);
        $rowa = mysqli_fetch_array($resulta);
        $img = "http://" . $_SERVER['SERVER_NAME'] . "/products/" . $rowa['imgpath'];
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . $key, $img);
        //副图（限制7）
        $sqlb = "select * from ph_img where proid='" . $row['ID'] . "' and btid > 0 order by px asc limit 0,7";
        $resultb = mysqli_query($conn,$sqlb);
        $i = 0;
        while ($rowb = mysqli_fetch_array($resultb)) {
            if (!empty($rowb['imgpath'])) {
                $i++;
                $img = "http://" . $_SERVER['SERVER_NAME'] . "/products/" . $rowb['imgpath'];
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . IntToChr($i) . $key, $img);
            }
        }
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AH' . $key, $row['fromurl']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AI' . $key, $value);
    }
}
/*
 * Job:导出无数据空白页面问题修改
 * 下面代码从IF中拿到外面
 */
ob_end_clean();
header("Pragma: public");
header("Expires: 0");
header('Cache-Control: max-age=0'); //禁止缓存
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename=' . $fileName . '.xls');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');


/**
 * 数字转字母 （类似于Excel列标）
 * @param Int $index 索引值
 * @param Int $start 字母起始值
 * @return String 返回字母
 */
function IntToChr($index, $start = 65)
{
    $str = '';
    if (floor($index / 26) > 0) {
        $str .= IntToChr(floor($index / 26) - 1);
    }
    return $str . chr($index % 26 + $start);
}

/**
 * 处理字符串中多个连续br替换一个
 */
function handContent($str = ''){
    $arr = array_filter(explode("<br/>",$str));
    return implode("<br/>",$arr);
}